// use crate::model::Fund;
use anyhow::anyhow;
use anyhow::Ok;
use anyhow::Result;
use chrono::Local;
use log::info;
use surrealdb::opt::PatchOp;
use surrealdb::sql::Datetime;
use surrealdb::sql::Thing;

use crate::model::security;
use crate::model::security::Security;
use crate::model::FudaRecord;

use super::FUDB;
use super::FUHLC;

///
/// update or create a security record
///
/// @param security: security's basic information
///
pub async fn upsert(security: &security::Security) -> Result<Option<FudaRecord>> {
    let id = security::Security::id_from(&security.market, &security.type_, &security.symbol)?;

    let old_security: Option<security::Security> = FUDB.select(("security", id.as_str())).await?;

    let mut updated: Option<FudaRecord> = None;
    let mut to_update = false;

    let timestamp = match &security.last_updated {
        Some(ts) => ts.clone(),
        None => format!("{}", FUHLC.new_timestamp()),
    };
    // Update a record with a specific ID
    let mut patch = FUDB.update(("security", &id)).patch(
        PatchOp::replace("/last_updated", timestamp)
    );

    match old_security {
        Some(Security { id: Some(t), .. }) => {
            updated = Some(FudaRecord { id: t });
        }
        _ => {
            patch = patch.patch(PatchOp::replace("/market", &security.market));
            patch = patch.patch(PatchOp::replace("/type_", &security.type_));
            patch = patch.patch(PatchOp::replace("/symbol", &security.symbol));
            to_update = true;
        }
    }

    if let Some(name) = &security.name {
        patch = patch.patch(PatchOp::replace("/name", name));
        to_update = true;
    }

    if let Some(ipo_date) = &security.ipo_date {
        patch = patch.patch(PatchOp::replace("/ipo_date", Datetime(*ipo_date)));
        to_update = true;
    }

    if to_update {
        updated = patch.await.map_err(|err| anyhow!(err))?;
    }

    Ok(updated)
}

/// find security
///
/// security_id: &str
///
///
pub async fn find(security_id: &str) -> Result<Security> {
    let id = Thing::from(("security", security_id));

    let security: Option<Security> = FUDB.query(r#"SELECT * FROM $id"#)
        .bind(("id", id)).await?
        .take(0)?;

    match security {
        Some(security) => Ok(security),
        None => Err(anyhow!(format!("Failed to find, Security not found for {}", security_id))),
    }
}
///
/// delete security by it's id , e.g. SH.STK.600519
///
pub async fn delete(security_id: &str) -> Result<Option<Security>> {
    let rs: Option<Security> = FUDB.delete(("security", security_id)).await?;
    Ok(rs)
}

pub async fn find_max_last_updated() -> Result<String> {
    let securities = select(&None, None, None, Some("last_updated DESC"), 0, 1).await?;
    match securities.first() {
        Some(s) =>
            match &s.last_updated {
                Some(v) => Ok(v.to_string()),
                None => Ok("".to_string()),
            }
        None => Ok("".to_string()),
    }
}
/// select securitiies
///
/// type_: SecurityType, security type
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select(
    market: &Option<security::Market>,
    type_: Option<security::SecurityType>,
    after_last_updated: Option<String>,
    order_by: Option<&str>,
    pi: u16,
    pn: u16
) -> Result<Vec<security::Security>> {
    let start_time = Local::now();

    let q_str_part = match (market, &type_, &after_last_updated) {
        (None, None, None) => "",
        (None, None, Some(_)) => "WHERE last_updated>$after_last_updated",
        (None, Some(_), None) => "WHERE type_=$type_",
        (None, Some(_), Some(_)) => "WHERE type_=$type_ AND last_updated>$after_last_updated",
        (Some(_), None, None) => "WHERE market=$market",
        (Some(_), None, Some(_)) => "WHERE market=$market AND last_updated>$after_last_updated",
        (Some(_), Some(_), None) => "WHERE market=$market AND type_=$type_",
        (Some(_), Some(_), Some(_)) =>
            "WHERE market=$market AND type_=$type_ AND last_updated>$after_last_updated",
    };
    let start = pi * pn;

    let order_by_ = if let Some(ob) = order_by { ob } else { "last_updated ASC" };
    let q_str = &format!(
        "SELECT * FROM security {} ORDER BY {} LIMIT {} START {}",
        q_str_part,
        order_by_,
        pn,
        start
    );

    // Fetch security data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    if let Some(t_) = &market {
        query = query.bind(("market", t_));
    }

    if let Some(t_) = &type_ {
        query = query.bind(("type_", t_));
    }
    if let Some(t_) = &after_last_updated {
        query = query.bind(("after_last_updated", t_));
    }

    // println!("query={:?}", query);

    let mut response = query.await?;

    // println!("\nresponse= {:?}\n", response);

    let values: Vec<security::Security> = response.take(0)?;


    let spent_time = Local::now().signed_duration_since(start_time);
    info!(
        "security::select  spent seconds={}.{} ",
        spent_time.num_seconds(),
        spent_time.num_milliseconds() % 1000
    );
    

    Ok(values)
}

#[cfg(test)]
mod test {
    use std::str::FromStr;

    use env_logger::fmt::Timestamp;

    use crate::db;
    use crate::model::security;
    use crate::model::security::SecurityType;
    use crate::model::security::Market;
    use crate::db::FUHLC;
    use crate::db::security::find_max_last_updated;

    #[tokio::test]
    async fn test_upsert_security() {
        let _ = db::init().await.unwrap();

        let security = security::Security {
            market: Market::SH,
            type_: security::SecurityType::KZZ,
            symbol: "security_code".to_string(),
            name: Some("security_name_abbr".to_string()),
            ..Default::default()
        };

        // Update a security record with a specific id
        let res = db::security::upsert(&security).await;

        println!("res {:?}", res)
    }

    /// Need to call first: test_update_kline_from_eastmoney to update klines for "513130"
    #[tokio::test]
    async fn test_select_securities() {
        // generate a timestamp
        let ts = FUHLC.new_timestamp();
        println!("\nts = {}", ts.to_string());

        let _ = db::init().await.unwrap();

        let result = db::security::select(
            &None,
            None,
            // None,
            // Some(uhlc::Timestamp::from_str("7389172880593416000/30201").unwrap()),
            Some("7389172880680234464/30201".to_string()),
            None,
            0,
            10
        ).await;

        // [00]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110059") }), market: SH, type_: KZZ, symbol: "110059", name: Some("浦发转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880611446272/30201) } market:SH is true type_:KZZ is true
        // [01]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110060") }), market: SH, type_: KZZ, symbol: "110060", name: Some("天路转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880628265360/30201) } market:SH is true type_:KZZ is true
        // [02]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110062") }), market: SH, type_: KZZ, symbol: "110062", name: Some("烽火转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880645651392/30201) } market:SH is true type_:KZZ is true
        // [03]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110063") }), market: SH, type_: KZZ, symbol: "110063", name: Some("鹰19转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880659111824/30201) } market:SH is true type_:KZZ is true
        // [04]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110064") }), market: SH, type_: KZZ, symbol: "110064", name: Some("建工转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880680234464/30201) } market:SH is true type_:KZZ is true
        // [05]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110067") }), market: SH, type_: KZZ, symbol: "110067", name: Some("华安转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880700751536/30201) } market:SH is true type_:KZZ is true
        // [06]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110068") }), market: SH, type_: KZZ, symbol: "110068", name: Some("龙净转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880718038768/30201) } market:SH is true type_:KZZ is true
        // [07]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110070") }), market: SH, type_: KZZ, symbol: "110070", name: Some("凌钢转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880741884432/30201) } market:SH is true type_:KZZ is true
        // [08]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110072") }), market: SH, type_: KZZ, symbol: "110072", name: Some("广汇转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880766043616/30201) } market:SH is true type_:KZZ is true
        // [09]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110073") }), market: SH, type_: KZZ, symbol: "110073", name: Some("国投转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880778945696/30201) } market:SH is true type_:KZZ is true
        // vs
        //[00]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110067") }), market: SH, type_: KZZ, symbol: "110067", name: Some("华安转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880700751536/30201) } market:SH is true type_:KZZ is true
        //[01]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110068") }), market: SH, type_: KZZ, symbol: "110068", name: Some("龙净转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880718038768/30201) } market:SH is true type_:KZZ is true
        //[02]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110070") }), market: SH, type_: KZZ, symbol: "110070", name: Some("凌钢转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880741884432/30201) } market:SH is true type_:KZZ is true
        //[03]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110072") }), market: SH, type_: KZZ, symbol: "110072", name: Some("广汇转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880766043616/30201) } market:SH is true type_:KZZ is true
        //[04]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110073") }), market: SH, type_: KZZ, symbol: "110073", name: Some("国投转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880778945696/30201) } market:SH is true type_:KZZ is true
        //[05]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110074") }), market: SH, type_: KZZ, symbol: "110074", name: Some("精达转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880799819248/30201) } market:SH is true type_:KZZ is true
        //[06]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110075") }), market: SH, type_: KZZ, symbol: "110075", name: Some("南航转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880821117984/30201) } market:SH is true type_:KZZ is true
        //[07]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110076") }), market: SH, type_: KZZ, symbol: "110076", name: Some("华海转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880838671520/30201) } market:SH is true type_:KZZ is true
        //[08]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110077") }), market: SH, type_: KZZ, symbol: "110077", name: Some("洪城转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880851199936/30201) } market:SH is true type_:KZZ is true
        //[09]= Security { id: Some(Thing { tb: "security", id: String("SH.KZZ.110079") }), market: SH, type_: KZZ, symbol: "110079", name: Some("杭银转债"), ipo_date: None, currency: None, lot_size: None, status: None, rank: None, last_updated: Some(7389172880866910928/30201) } market:SH is true type_:KZZ is true

        println!("result= {:?}\n", result);
        assert_eq!(result.is_ok(), true);

        if let Ok(values) = &result {
            for (index, value) in values.iter().enumerate() {
                println!(
                    "[{:02}]= {:?} market:{} is {} type_:{} is {}",
                    index,
                    value,
                    value.market,
                    matches!(value.market, Market::SH | Market::SZ | Market::BJ),
                    value.type_,
                    matches!(
                        value.type_,
                        SecurityType::STK |
                            SecurityType::ETF |
                            SecurityType::KZZ |
                            SecurityType::OPF
                    )
                );
            }
        }
    }

    #[tokio::test]
    async fn test_find_max_last_updated() {
        let _ = db::init().await.unwrap();

        let res = find_max_last_updated().await;
        println!("res= {:?}", res);
    }
}
